		**********************************************************
		** File: cleaning_ICRG.do								**
		** Paper: Human Trafficking Indicators: A New Dataset	**
		** Author: Richard Frank								**
		** Date: July 11, 2021									**
		** Task: Cleaning ICRG									**
 		**********************************************************
 

	clear
	version 16.1
	set seed 1234
	
	cd "~"
	
	* Data source: https://epub.prsgroup.com/products/icrg/icrg-historical-data
	
	** EXTERNAL CONFLICT **
	
	import excel "ICRGsDataset2017.xls", sheet("E-External Conflict") cellrange(A8:AH154) clear
	rename B y1984
	rename C y1985
	rename D y1986
	rename E y1987
	rename F y1988
	rename G y1989
	rename H y1990	
	rename I y1991	
	rename J y1992	
	rename K y1993	
	rename L y1994	
	rename M y1995
	rename N y1996
	rename O y1997
	rename P y1998
	rename Q y1999	
	rename R y2000
	rename S y2001	
	rename T y2002	
	rename U y2003		
	rename V y2004
	rename W y2005
	rename X y2006
	rename Y y2007
	rename Z y2008
	rename AA y2009
	rename AB y2010
	rename AC y2011
	rename AD y2012		
	rename AE y2013			
	rename AF y2014			
	rename AG y2015	
	rename AH y2016
	destring y2007, replace
	rename A Country
	drop in 1
	reshape long y, i(Country) j(year)
	rename y conflict_external
	run "cow.do"
	tab Country if ccode==0
	drop if ccode==0
	browse if ccode==365 & year==1991
	rename Country country
	duplicates report ccode year
	sort ccode year
	browse if ccode==ccode[_n-1] & year==year[_n-1]
	drop if country=="Hong Kong"
	drop if country=="East Germany" & year>1989
	drop if country=="USSR" & year>1991
	drop if country=="Serbia *" & year<2011
	drop if country=="Russia" & year<1992
	drop if country=="Serbia & Montenegro" & year>2010
	order ccode
	
	save "ICRG_conflict_external.dta", replace

	** INTERNAL CONFLICT **
	
	import excel "ICRGsDataset2017.xls", sheet("D-Internal Conflict") cellrange(A8:AH154) clear
	rename B y1984
	rename C y1985
	rename D y1986
	rename E y1987
	rename F y1988
	rename G y1989
	rename H y1990	
	rename I y1991	
	rename J y1992	
	rename K y1993	
	rename L y1994	
	rename M y1995
	rename N y1996
	rename O y1997
	rename P y1998
	rename Q y1999	
	rename R y2000
	rename S y2001	
	rename T y2002	
	rename U y2003		
	rename V y2004
	rename W y2005
	rename X y2006
	rename Y y2007
	rename Z y2008
	rename AA y2009
	rename AB y2010
	rename AC y2011
	rename AD y2012		
	rename AE y2013			
	rename AF y2014			
	rename AG y2015	
	rename AH y2016
	destring y2007, replace
	rename A Country
	drop in 1
	reshape long y, i(Country) j(year)
	rename y conflict_internal
	run "cow.do"
	tab Country if ccode==0
	drop if ccode==0
	browse if ccode==365 & year==1991
	rename Country country
	duplicates report ccode year
	sort ccode year
	browse if ccode==ccode[_n-1] & year==year[_n-1]
	drop if country=="Hong Kong"
	drop if country=="East Germany" & year>1989
	drop if country=="USSR" & year>1991
	drop if country=="Serbia *" & year<2011
	drop if country=="Russia" & year<1992
	drop if country=="Serbia & Montenegro" & year>2010
	order ccode
	save "ICRG_conflict_internal.dta", replace
	 
	*** Ethnic tensions **
	
	import excel "ICRGsDataset2017.xls", sheet("J-Ethnic Tensions") cellrange(A8:AH154) clear
	rename B y1984
	rename C y1985
	rename D y1986
	rename E y1987
	rename F y1988
	rename G y1989
	rename H y1990	
	rename I y1991	
	rename J y1992	
	rename K y1993	
	rename L y1994	
	rename M y1995
	rename N y1996
	rename O y1997
	rename P y1998
	rename Q y1999	
	rename R y2000
	rename S y2001	
	rename T y2002	
	rename U y2003		
	rename V y2004
	rename W y2005
	rename X y2006
	rename Y y2007
	rename Z y2008
	rename AA y2009
	rename AB y2010
	rename AC y2011
	rename AD y2012		
	rename AE y2013			
	rename AF y2014			
	rename AG y2015	
	rename AH y2016
	destring y2007, replace
	rename A Country
	drop in 1
	reshape long y, i(Country) j(year)
	rename y ethnic_tension
	run "cow.do"
	tab Country if ccode==0
	drop if ccode==0
	browse if ccode==365 & year==1991
	rename Country country
	duplicates report ccode year
	sort ccode year
	browse if ccode==ccode[_n-1] & year==year[_n-1]
	drop if country=="Hong Kong"
	drop if country=="East Germany" & year>1989
	drop if country=="USSR" & year>1991
	drop if country=="Serbia *" & year<2011
	drop if country=="Russia" & year<1992
	drop if country=="Serbia & Montenegro" & year>2010
	order ccode
	save "ICRG_ethnic_tension.dta", replace
	merge 1:1 ccode year using "ICRG_conflict_external"
	drop _merge
	merge 1:1 ccode year using "ICRG_conflict_internal"	 
	drop _merge
	label var ethnic_tension "Ethnic tension (ICRG)"
	label var conflict_external "External conflict (ICRG)"
	label var conflict_internal "Internal conflict (ICRG)"
	
	save "ICRG_conflict.dta", replace
	 
	 
	
